#!/usr/bin/perl

# use strict;
# use warnings;

# Declare subroutines
# sub trim($);
# sub getdb($);
# sub getfnm($);
# sub dostuff();

my $level = 0;
my ($repository,
	$database,
	$filename,
	$location) = "";
	
# Gimmick
print "\nCLI SQL Procedure Analyzer [Version 1.2]\n";
print "Dynamic SQL, Conditional self-calls and PBA not yet supported\n";
print "--------------------------------------------------------------\n\n";
	
# Get user input
print "Type in your local repository path (Root directory): ";
chomp($repository = <STDIN>);
print "Type in the target database: ";
chomp($database = <STDIN>);
print "Type in the Procedure name (.sql not required): ";
chomp($filename = <STDIN>);

print "\n";

# Execute the application function
my @list = ($repository, $database, $filename, $level);
dostuff(@list);

# function to trim all leading and trailing whitespaces
sub trim($) {
	my $line = shift;
	
	# Remove leading whitespace characters
	$line =~ s/^\s+//;
	
	# Remove trailing whitespace characters
	$line =~ s/\s+$//;
	
	return $line;
}

# function to extract database name
sub getdb($) {
	# for debugging purpose
	# print "$_[0] <-> $_[1]\n";
	
	my $database = $_[0];
	# regular expression to extract database name
	if($database !~ m/\.{2}/) {
		$database = $_[1];
	} elsif ($database =~ m/(\w+)(\s+)(@)(\w+)(\s*)(=)(\s*)(\w+)(\.{2})(.+)/) {
		$database = $8;
	} elsif($database =~ m/(\w+)(\s+)(\w+)(\.{2})(.+)/) {
		$database = $3;
	}
	
	return $database;
}

# function to extract filename
sub getfnm($) {
	my $filename = shift;
	# regular expression to extract database name
	if($filename !~ m/\.{2}/) {
		if($filename =~ m/^(\w+)(\s+)(\S+)$/) {
			$filename = $3;
		} elsif ($filename =~ m/^(\w+)(\s+)(@)(\S+)(\s*)(=)(\s*)(\S+)(.*)/) {
			$filename = $8;
		} elsif ($filename =~ m/(\w+)(\s+)(\S+)(\s+)(.+)/) {
			$filename = $3;
		}
	} elsif ($filename =~ m/(\w+)(\s+)(@)(\w+)(\s*)(=)(\s*)(\w+)(\.{2})(\S+)(.*)/) {
		$filename = $10;
	} elsif($filename =~ m/(\w+)(\s+)(\w+)(\.{2})(\S+)(.*)/) {
		$filename = $5;
	}
	
	return $filename;
}

sub dostuff {
	# Build absolute path
	my ($repository,
		$database_i,
		$filename_f,
		$level) = @_;
	my ($database_f,
		$filename_i,
		$builder,
		@fp,
		$i,
		$been_here
		);
	
	$filename_i = $filename_f;
	$been_here = 0; # indicates whether level has already been visited
		
	my $location = $repository.("/objects/").$database_i.("/").$filename_f.(".sql");
	
	# for debugging purpose
	# print "location: $location\n";
	
	my $line_num = 1;
	
	# Open the file for reading
	open FILE, "<", $location or die $!;
	
	# Get the contents of file into '@fp'
	@fp = <FILE>;

	for($i = 0; @fp[$i]; $i++) {
		if(@fp[$i] =~ m/exec(ute|)/i && @fp[$i] !~ m/grant/i && @fp[$i] !~ m/^(-)(-)/ && @fp[$i] !~ m/(\w+)(\s+)[(](@)(\S+)[)]/) {
			if($level > 0 && $been_here == 0) {
				print "[pipeline] : level up\n";
			}
			
			$been_here++;
			
			@fp[$i] = trim(@fp[$i]);
			print "[level $level]\n";
			$builder = $builder.("[line ").$line_num.("] : ").@fp[$i].("\n");
			print $builder;
			
			#Get the database and file name
			$database_f = &getdb(@fp[$i], $database_i);
			$filename_f = getfnm(@fp[$i]);
			
			# for debugging purpose
			# print "\n----------------------------\n";
			# print "database_i: $database_i\n";
			# print "database_f: $database_f\n";
			# print "filename_i: $filename_i\n";
			# print "filename_f: $filename_f\n";
			# print "----------------------------\n";
			
			if($database_f eq $database_i && $filename_f eq $filename_i) {
				print "\n";
				print "----------------------------------\n";
				print "Endless recursion if processed...\n";
				print "Manual lookup required\n";
				print "----------------------------------\n";
				
				next ITERATION;
			}			
			
			print "database: $database_f\n";
			print "filename: $filename_f\n\n";
			
			# Increment level
			$level++;
			
			# Recursive call
			my @list = ($repository, $database_f, $filename_f, $level);
			# dostuff(@list);
			
			# Decrement level
			$level--;
			
			print "[pipeline] : level down\n";
			
			# for debugging purpose
			# print "I am at level $level with location $location\n";
		}
		
		ITERATION:
		# Increment line number
		$line_num++;
	}
}